<?xml version="1.0" encoding="utf-8"?>
<root xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="statements.xsd">
	<module name="system_code" description="维护系统代码表" >
		<statement name="find_categories" resultType="list-string">
			<![CDATA[
			select distinct `type` from sys_code order by `type` 
			]]>
		</statement>
		<statement name="find"   resultType="table" >
			<![CDATA[
			select code_id as codeId, `type`, `key`, `value`, parent_key as parentKey from sys_code
			]]>
			<dynamic prepend="where">
				<isNotNull parameter="category_id">
					<![CDATA[`type` = #category_id#]]>
				</isNotNull>
			</dynamic>
			<![CDATA[order by `key`]]>
			<parameter name="category_id" type="int" />
		</statement>

		<statement name="find_by_code_id"   resultType="table" >
			<![CDATA[
			select code_id as codeId,`type`, `key`, `value`, parent_key as parentKey from sys_code
			where code_id =#code_id#
			]]>
			<parameter name="code_id" type="string" />
		</statement>
		<statement name="find_syscode_info"  allowPaging="true"  resultType="table" >
			<![CDATA[
			select code_id as codeId,`type`, `key`, `value`, parent_key as parentKey from sys_code
			]]>
			<dynamic prepend="where">
				<isNotNull parameter="category_id">
					<![CDATA[`type` = #category_id#]]>
				</isNotNull>
				<isNotEmpty parameter="key">
					<![CDATA[
					`key` like  concat('%', #key#, '%')
					]]>
				</isNotEmpty>
			</dynamic>
			<![CDATA[order by `key`]]>
			<parameter name="category_id" type="int" />
			<parameter name="key" type="string" matchType="contains"/>
		</statement>
		<statement name="find_syscode_info_count" resultType="int" >
			<![CDATA[
			select count(*) from sys_code
			]]>
			<dynamic prepend="where">
				<isNotNull parameter="category_id">
					<![CDATA[`type` = #category_id#]]>
				</isNotNull>
				<isNotEmpty parameter="key">
					<![CDATA[
					`key` like  concat('%', #key#, '%')
					]]>
				</isNotEmpty>
			</dynamic>
			<parameter name="category_id" type="int" />
			<parameter name="key" type="string" matchType="contains"/>
		</statement>
		<statement name="create">
			<![CDATA[
			insert into sys_code
			(
			code_id,
			`type`,
			`key`,
			`value`,
			parent_key
			)
			values
			(
			#code_id#,
			#type#,
			#key#,
			#value#,
			#parent_key#
			)			
			]]>
			<parameter name="code_id" type="string"/>
			<parameter name="type" type="int"/>
			<parameter name="key" type="string"/>
			<parameter name="value" type="string"/>
			<parameter name="parent_key" type="string"/>
		</statement>
		<statement name="update">
			<![CDATA[
			update sys_code
			set  `key` = 	#key#,
	            `value`=	#value#,
		    parent_key = #parent_key#
			where code_id = #code_id#			
			]]>
			<parameter name="code_id" type="string"/>
			<parameter name="key" type="string"/>
			<parameter name="value" type="string"/>
			<parameter name="parent_key" type="string"/>
		</statement>
		<statement name="delete">
			<![CDATA[
			delete from  sys_code  where code_id = #code_id#			
			]]>
			<parameter name="code_id" type="string"/>
		</statement>

		<statement name="validate_duplicated_key" resultType="bool" >
			<![CDATA[
			select exists(
			select `key` from sys_code
			where `key` = #key#
			and `type` = #type#
			and code_id <> #code_id#)
			]]>
			<parameter name="key" type="string" />
			<parameter name="type" type="int" />
			<parameter name="code_id" type="string" />
		</statement>

		<statement name="validate_duplicated_value" resultType="bool" >
			<![CDATA[
			select exists(
			select `value` from sys_code
			where `value` = #value#
			and `type` = #type#
			and code_id <> #code_id#)
			]]>
			<parameter name="value" type="string" />
			<parameter name="type" type="int" />
			<parameter name="code_id" type="string" />
		</statement>

		<statement name="validate_exist_key" resultType="bool" >
			<![CDATA[
			select exists(
			select `key` from sys_code
			where `key` = #key#
			and `type` = #type#)
			]]>
			<parameter name="key" type="string" />
			<parameter name="type" type="int" />
		</statement>

		<statement name="validate_exist_value" resultType="bool" >
			<![CDATA[
			select exists(
			select `value` from sys_code
			where `value` = #value#
			and `type` = #type#)
			]]>
			<parameter name="value" type="string" />
			<parameter name="type" type="int" />
		</statement>
	</module>
</root>
